package beautiful.butterfly.drds.binlog.utils;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

@Slf4j
public class SqlUtils
{


    private static final String TABLE_SCHEMA = "TABLE_SCHEMA";
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String COLUMN_NAME = "COLUMN_NAME";
    private static final String ORDINAL_POSITION = "ORDINAL_POSITION";
    // 1)JSqlParser
    // http://search.maven.org/#search%7Cga%7C1%7Cjsqlparser
    // https://sourceforge.net/projects/jsqlparser/files/ [old]
    // https://github.com/JSQLParser/JSqlParser
    // https://github.com/JSQLParser/JSqlParser/wiki
    //
    //
    //
    private static final ThreadLocal<TGSqlParser> localTGSqlParsers = new ThreadLocal<TGSqlParser>()
    {
        protected TGSqlParser initialValue()
        {
            return new TGSqlParser(EDbVendor.dbvmysql);
        }
    };
    private static final String name = "com.mysql.jdbc.Driver";
    private static String keyStr = null;
    private static String pointStr = null;

    static
    {
        try
        {
            Class.forName(name);
        } catch (ClassNotFoundException e)
        {
            log.info(e.toString());
            System.exit(-1);
        }
        //
        byte[] key = new byte[1];
        key[0] = 0x60;
        keyStr = new String(key);
        key = new byte[1];
        key[0] = 0x2E;
        pointStr = new String(key);
    }
    // 2)SqlParser
    // http://www.sqlparser.com/
    // https://github.com/sushilshah/gsp-sqlparser
    // https://github.com/sushilshah/gsp-sqlparser/blob/master/demos/getstatement/getstatement.java

    private static void getPrimaryKeyMapping(Connection connection, String databaseName, String tableName,
                                             HashMap<String, ArrayList<String>> primaryKeysMapping) throws Exception
    {
        // 初始化为空，表示不存在主键
        ArrayList<String> pks = new ArrayList<String>();
        // 准备sql语句
        String sql = " select k.column_name ";
        sql += " from information_schema.table_constraints t ";
        sql += " join information_schema.key_column_usage k ";
        sql += " using (constraint_name,table_schema,table_name) ";
        sql += " where t.constraint_type='primary key' ";
        sql += " and t.table_schema='" + databaseName + "' ";
        sql += " and t.table_name='" + tableName + "' ";
        sql += " order by k.column_name";
        // 开始查询
        Statement statement = null;
        ResultSet resultSet = null;
        String columnName;
        try
        {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (null != resultSet && resultSet.next())
            {
                columnName = resultSet.getString(COLUMN_NAME);
                pks.add(columnName);
            }
        } catch (Exception e)
        {
            throw e;
        } finally
        {
            // 必须分开关闭
            // 关闭resultset
            if (null != resultSet)
            {
                try
                {
                    resultSet.close();
                } catch (SQLException e)
                {
                }
            }
            // 关闭stmt
            if (null != statement)
            {
                try
                {
                    statement.close();
                } catch (SQLException e)
                {
                }
            }
        }
        // LoggerUtils.debug(logger, "pks:[" + database + "][" + tableName + "] ---"
        // + pks);
        primaryKeysMapping.put(StringUtils.union(databaseName, tableName), pks);// 更新此数据
        // 返回
    }

    public static HashMap<String, String> getDatabaseTableColumnsMapping(//
                                                                         String ip,//
                                                                         int port,//
                                                                         String username,//
                                                                         String password,//
                                                                         String databaseName,//
                                                                         String tableName,//
                                                                         HashMap<String, ArrayList<String>> primaryKeysMapping)//
            throws Exception
    {
        // 通过查询数据库来获取消息
        // LoggerUtils.debug(logger, "-------execute
        // getDatabaseNameAndTableNameToPositionToColumnNameMapMap ... pity...because database-tableName ["
        // + database + "-" + tableName + " ] structure is changed...");
        HashMap<String, String> mappings = new HashMap<String, String>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try
        {
            String url = "jdbc:mysql://" + ip + ":" + port + "/" + databaseName;
            connection = DriverManager.getConnection(url, username, password);// 获取连接
            statement = connection.createStatement();
            // 构造sql
            String sql = "select COLUMN_NAME, ORDINAL_POSITION ";
            sql += " from INFORMATION_SCHEMA.COLUMNS ";
            sql += "where TABLE_SCHEMA='" + databaseName + "' and TABLE_NAME='" + tableName + "'";
            sql += " order by ORDINAL_POSITION";
            // LoggerUtils.debug(logger, "sql:" + sql);
            resultSet = statement.executeQuery(sql);
            String columnName;
            int position;
            while (null != resultSet && resultSet.next())
            {
                // 存在下一行数据
                // String tableSchema = resultSet.getString(TABLE_SCHEMA);
                // String tableSchema = resultSet.getString(TABLE_SCHEMA);
                columnName = resultSet.getString(COLUMN_NAME);
                position = resultSet.getInt(ORDINAL_POSITION);
                // LoggerUtils.debug(logger, "" + columnName + " " + position);
                mappings.put("" + (position - 1), columnName);
            }
            // 借着这个connection,获取主键关系
            getPrimaryKeyMapping(connection, databaseName, tableName, primaryKeysMapping);
            log.info("connect to mysql to get meta mapping succeed..." + ip + ":" + port);
        } catch (Exception e)
        {
            log.info(
                    "connect to mysql to get meta mapping fail..." + ip + ":" + port + " " + e.toString());
            throw e;
        } finally
        {
            // 必须分开关闭
            // 关闭resultset
            if (null != resultSet)
            {
                try
                {
                    resultSet.close();
                } catch (SQLException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            // 关闭stmt
            if (null != statement)
            {
                try
                {
                    statement.close();
                } catch (SQLException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            // 关闭conn
            if (null != connection)
            {
                try
                {
                    connection.close();
                } catch (SQLException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        // LoggerUtils.debug(logger, "-----------------------------query mapping
        // end");
        return mappings;
    }

    public static boolean isAlterTableSql(String sql)
    {
        sql = sql.toUpperCase();
        return sql.startsWith("alter tablename");
    }

    public static boolean isDropTableSql(String sql)
    {
        sql = sql.toUpperCase();
        return sql.startsWith("drop tablename");
    }

    public static String getTableNameBySqlParser(String sql)
    {

        String result = null;
        // 1)准备工作
        TGSqlParser sqlParser = localTGSqlParsers.get();
        sqlParser.sqltext = sql;
        TTable table;
        // 2)准备解析
        if (0 == sqlParser.parse() && sqlParser.sqlstatements.size() > 0)
        {
            // succeed
            // gudusoft.gsqlparser.ESqlStatementType sqlstatementtype;
            // LoggerUtils.debug(logger, "parse sql succeed");
            TCustomSqlStatement stmt = sqlParser.sqlstatements.get(0);
            switch (stmt.sqlstatementtype)
            {
                case sstaltertable:
                case sstdroptable:
                    table = stmt.getFirstPhysicalTable();
                    if (null != table)
                    {
                        result = table.getName();
                    }
                    break;
                default:// 其它的暂时不支持,需要的话再扩展
                    break;
            }
        } else
        {
            log.info("fail to parse " + sql + ",will try another method");
        }

        // 3)如果为null,做一些补救措施
        if (null == result)
        {
            String[] strArray = sql.split(" ");
            if (null != strArray && strArray.length >= 3)
            {
                result = strArray[2];
                // printString(result);
                // 可能存在.字符
                int index = result.indexOf(pointStr);
                if (-1 != index)
                {
                    result = result.substring(index + 1);
                }
            }
        }

        // 4)规整化,可能有\0X60字符
        if (null != result)
        {
            log.info("succeed to get tableName name...");
            // System.out.println(result.getBytes()[0]);
            if (0 == result.indexOf(keyStr))
            {
                result = result.substring(1);
            }
            if (result.length() - 1 == result.indexOf(keyStr))
            {
                result = result.substring(0, result.length() - 1);
            }
        }

        return result;
    }

    private static void printString(String str)
    {
        byte[] bytes = str.getBytes();
        for (int index = 0; index < bytes.length; index++)
        {
            System.out.println(bytes[index]);
        }
    }

    public static void main(String[] args)
    {
        String sql = "drop tableName tab1 ";
        System.out.println(getTableNameBySqlParser(sql));
        sql = "drop tableName `tab1` ";
        System.out.println(getTableNameBySqlParser(sql));
        sql = "alter tableName http_monitor add column statistic char(1) DEFAULT NULL COMMENT '234'";
        System.out.println(getTableNameBySqlParser(sql));
        sql = "Alter tableName `skyeye`.`servicekey_product`     drop column `subkey`";
        System.out.println(getTableNameBySqlParser(sql));
        sql = "Alter tableName `servicekey_product`     drop column `subkey`";
        System.out.println(getTableNameBySqlParser(sql));
        sql = "Alter tableName `skyeye`.`risk_threshold_monitor`    change `pcode` `product_code` char(50) CHARSET utf8 COLLATE utf8_general_ci NOT NULL";
        System.out.println(getTableNameBySqlParser(sql));
    }

    public synchronized static void init()
    {
        // 触发static块
    }
}
